﻿using org.in2bits.MyXls;
using System.Collections.Generic;
using System.Data;
using System.Reflection;
using YanZhiwei.DotNet2.Utilities.Common;
namespace YanZhiwei.DotNet.MyXls0._6.Utilities
{
    public class MyxlsHelper
    {
        /// <summary>
        /// 将集合导出到excel
        /// </summary>
        /// <typeparam name="T">实体类行</typeparam>
        /// <param name="exceldbSource">数据源</param>
        /// <param name="savePath">保存路径</param>
        public static void ToExecel<T>(IList<T> excelDbSource, string savePath, string sheetName) where T : class
        {
            int _recordCnt = excelDbSource.Count;
            XlsDocument _xls = new XlsDocument();
            string _savePath = savePath.Substring(0, savePath.LastIndexOf(@"\") + 1);
            _xls.FileName = savePath.Substring(savePath.LastIndexOf(@"\") + 1);

            // 列标题样式
            XF _colXF = _xls.NewXF(); // 为xls生成一个XF实例，XF是单元格格式对象
            _colXF.HorizontalAlignment = HorizontalAlignments.Centered;
            _colXF.VerticalAlignment = VerticalAlignments.Centered;
            _colXF.UseBorder = true;
            _colXF.TopLineStyle = 1;
            _colXF.TopLineColor = Colors.Grey;
            _colXF.BottomLineStyle = 1;
            _colXF.BottomLineColor = Colors.Grey;
            _colXF.LeftLineStyle = 1;
            _colXF.LeftLineColor = Colors.Grey;
            _colXF.Pattern = 1; // 单元格填充风格。如果设定为0，则是纯色填充(无色)，1代表没有间隙的实色
            _colXF.PatternBackgroundColor = Colors.White;
            _colXF.PatternColor = Colors.Silver;
            _colXF.Font.Bold = true;
            _colXF.Font.Height = 12 * 20;

            // 数据单元格样式
            XF _dataXF = _xls.NewXF(); // 为xls生成一个XF实例，XF是单元格格式对象
            _dataXF.HorizontalAlignment = HorizontalAlignments.Centered;
            _dataXF.VerticalAlignment = VerticalAlignments.Centered;
            _dataXF.UseBorder = true;
            _dataXF.LeftLineStyle = 1;
            _dataXF.LeftLineColor = Colors.Black;
            _dataXF.BottomLineStyle = 1;
            _dataXF.BottomLineColor = Colors.Black;
            _dataXF.UseProtection = false; // 默认的就是受保护的，导出后需要启用编辑才可修改
            _dataXF.TextWrapRight = true; // 自动换行

            Worksheet _sheet = _xls.Workbook.Worksheets.Add(sheetName);
            int _nCelIndex = 0, _nRowIndex = 1;
            Cells _cells = _sheet.Cells;
            IDictionary<string, string> _fields = ReflectHelper.GetDisplayName<T>();
            string[] _columns = new string[_fields.Count];
            _fields.Values.CopyTo(_columns, 0);
            foreach (string col in _columns)
            {
                _nCelIndex++;
                _cells.Add(1, _nCelIndex, col, _colXF);
            }
            foreach (T t in excelDbSource)
            {
                _nRowIndex++;
                _nCelIndex = 0;
                foreach (KeyValuePair<string, string> entry in _fields)
                {
                    _nCelIndex++;
                    object _value = typeof(T).InvokeMember(entry.Key, BindingFlags.GetProperty, null, t, null);
                    _cells.Add(_nRowIndex, _nCelIndex, _value == null ? string.Empty : _value.ToString(), _dataXF);
                }
            }
            _xls.Save(_savePath, true);
        }
        /// <summary>
        /// 将EXCEL导出到DataTable
        /// </summary>
        /// <param name="excelPath">excel路径</param>
        /// <param name="sheetIndex">Worksheets『从0开始』</param>
        /// <param name="startRowIndex">遍历起始行『从0开始』</param>
        /// <param name="startColIndex">遍历起始列『从0开始』</param>
        /// <returns></returns>
        public static DataTable ToDataTable(string excelPath, int sheetIndex, int rowStartIndex)
        {
            XlsDocument _excelDoc = new XlsDocument(excelPath);
            DataTable _table = new DataTable();
            Worksheet _sheet = _excelDoc.Workbook.Worksheets[sheetIndex];
            ushort _colCount = _sheet.Rows[1].CellCount;
            ushort _rowCount = (ushort)_sheet.Rows.Count;

            for (ushort j = 1; j <= _colCount; j++)
            {
                _table.Columns.Add(new DataColumn(j.ToString()));
            }
            for (ushort i = 1; i <= _rowCount; i++)
            {
                DataRow _row = _table.NewRow();
                for (ushort j = 1; j <= _colCount; j++)
                {
                    _row[j - 1] = _sheet.Rows[i].GetCell(j).Value;
                }
                _table.Rows.Add(_row);
            }
            return _table;
        }
        public delegate void LoopExcelRule(string colName, int colIndex, int rowIndex, object value);
        /// <summary>
        /// 遍历excel数据
        /// </summary>
        /// <param name="excelPath">excel路径</param>
        /// <param name="sheetIndex">Worksheets『从0开始』</param>
        /// <param name="startRowIndex">遍历起始行『从0开始』</param>
        /// <param name="startColIndex">遍历起始列『从0开始』</param>
        /// <param name="loopExcelRule">遍历规则『委托』</param>
        public static void LoopExcel(string excelPath, int sheetIndex, ushort startRowIndex, ushort startColIndex, LoopExcelRule loopExcelRule)
        {

            XlsDocument _excelDoc = new XlsDocument(excelPath);
            Worksheet _sheet = _excelDoc.Workbook.Worksheets[sheetIndex];
            int _colCount = _sheet.Rows[startRowIndex].CellCount;
            int _rowCount = _sheet.Rows.Count;
            for (ushort i = startRowIndex; i < _rowCount; i++)
            {
                for (ushort j = startColIndex; j <= _colCount; j++)
                {
                    string _colName = _sheet.Rows[i].GetCell(j).Value.ToString();
                    object _value = _sheet.Rows[i].GetCell(j).Value;
                    loopExcelRule(_colName, j, i, _value);
                }
            }
        }

        public delegate void LoopExcelRule2(Row row);
        public static void LoopExcel(string excelPath, int sheetIndex, ushort startRowIndex, LoopExcelRule2 loopExcelRule)
        {
            XlsDocument _excelDoc = new XlsDocument(excelPath);

            Worksheet _sheet = _excelDoc.Workbook.Worksheets[sheetIndex];

            int _colCount = _sheet.Rows[startRowIndex].CellCount;
            int _rowCount = _sheet.Rows.Count;
            for (ushort i = startRowIndex; i < _rowCount; i++)
            {
                Row _rows = _sheet.Rows[i];
                loopExcelRule(_rows);
            }

        }
        public static void LoopExcel(string excelPath, string sheetName, ushort startRowIndex, LoopExcelRule2 loopExcelRule)
        {
            XlsDocument _excelDoc = new XlsDocument(excelPath);
            Worksheet _sheet = _excelDoc.Workbook.Worksheets[sheetName];
            int _colCount = _sheet.Rows[startRowIndex].CellCount;
            int _rowCount = _sheet.Rows.Count;
            for (ushort i = startRowIndex; i < _rowCount; i++)
            {
                Row _rows = _sheet.Rows[i];
                loopExcelRule(_rows);
            }
        }
    }
}
